﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.EntityClient;
using System.Data.Objects;
using System.Data.Objects.DataClasses;
using System.Data.SqlClient;
using System.Text;
using System.Data.Metadata.Edm;
using System.Linq;

namespace EntityGraph.SQL
{
    internal abstract class Sql
    {
        public abstract void ToStringBuilder(StringBuilder sb);

        public sealed override string ToString()
        {
            StringBuilder sb = new StringBuilder();
            this.ToStringBuilder(sb);
            return sb.ToString(); ;
        }
    }
    internal class SqlColumn : Sql
    {
        public string Table { get; set; }
        public string Name { get; set; }
        public SqlColumn(string table, string name)
        {
            this.Table = table;
            this.Name = name;
        }
        public override void ToStringBuilder(StringBuilder sb)
        {
            sb.AppendFormat("{0}.{1}", Table, Name);
        }
    }
    internal class SqlColumnSelect : SqlColumn
    {
        public string AsName { get; private set; }
        public SqlColumnSelect(string table, string name, string asName)
            : base(table, name)
        {
            this.AsName = asName;
        }
        public override void ToStringBuilder(StringBuilder sb)
        {
            sb.AppendFormat("{0}.{1} AS {2}", Table, Name, AsName);
        }
    }
    internal class SqlCase : SqlColumnSelect
    {
        public List<SqlWhen> Whens { get; private set; }
        public SqlCase(string asName, List<SqlWhen> whens)
            : base(null, null, asName)
        {
            this.Whens = whens;
        }
        public override void ToStringBuilder(StringBuilder sb)
        {
            sb.AppendLine("CASE");
            foreach(var when in Whens)
            {
                sb.Append("   ");
                when.ToStringBuilder(sb);
                sb.AppendLine();
            }
            sb.AppendLine("END");
            sb.AppendLine("AS " + AsName);
        }
    }
    internal class IsNotNull : SqlUnaryExpression
    {
        public SqlColumnExpression ColumnExpression { get; private set; }
        public IsNotNull(SqlColumnExpression collumnExpression)
        {
            this.ColumnExpression = collumnExpression;
        }
        public override void ToStringBuilder(StringBuilder sb)
        {
            ColumnExpression.ToStringBuilder(sb);
            sb.Append(" is not null");
        }
    }
    internal class SqlWhen : SqlExpression
    {
        public SqlExpression Expression { get; private set; }
        public SqlConstantExpression WhenTrue { get; private set; }
        public SqlWhen(SqlExpression expression, SqlConstantExpression whenTrue)
        {
            this.Expression = expression;
            this.WhenTrue = whenTrue;
        }
        public override void ToStringBuilder(StringBuilder sb)
        {
            sb.Append("WHEN ");
            Expression.ToStringBuilder(sb);
            sb.Append(" THEN ");
            WhenTrue.ToStringBuilder(sb);
        }
    }
    internal abstract class SqlExpression : Sql { }
    internal abstract class SqlUnaryExpression : SqlExpression { }
    internal abstract class SqlBinaryExpression : SqlExpression { }
    internal class SqlColumnExpression : SqlUnaryExpression
    {
        public SqlColumn Column { get; private set; }
        public SqlColumnExpression(SqlColumn column)
        {
            this.Column = column;
        }

        public override void ToStringBuilder(StringBuilder sb)
        {
            Column.ToStringBuilder(sb);
        }
    }
    internal class SqlConstantExpression : SqlUnaryExpression
    {
        public object Value { get; private set; }
        public SqlConstantExpression(object value)
        {
            this.Value = value;
        }
        public override void ToStringBuilder(StringBuilder sb)
        {
            sb.Append(Value.ToString());
        }
    }
    internal class SqlEqualityExpression : SqlBinaryExpression
    {
        public SqlUnaryExpression Lhs { get; private set; }
        public SqlUnaryExpression Rhs { get; private set; }
        public SqlEqualityExpression(SqlUnaryExpression lhs, SqlUnaryExpression rhs)
        {
            this.Lhs = lhs;
            this.Rhs = rhs;
        }
        public override void ToStringBuilder(StringBuilder sb)
        {
            Lhs.ToStringBuilder(sb);
            sb.Append("=");
            Rhs.ToStringBuilder(sb);
        }
    }

    internal class SqlTable : Sql
    {
        public string Name { get; private set; }
        public string AsName { get; private set; }
        public string Key { get; private set; }
        public SqlTable(string name, string asName, string key)
        {
            this.Name = name;
            this.AsName = asName;
            this.Key = key;
        }
        public override void ToStringBuilder(StringBuilder sb)
        {
            sb.AppendFormat("{0} AS {1}", Name, AsName);
        }
    }
    internal class SqlLeftOuterJoin : SqlTable
    {
        public SqlTable Left { get; private set; }
        public SqlTable Right { get; private set; }
        public SqlExpression OnExpression { get; private set; }
        public SqlLeftOuterJoin(SqlTable left, SqlTable right, SqlExpression onExpression)
            : base(null, null, null)
        {
            this.Left = left;
            this.Right = right;
            this.OnExpression = onExpression;
        }
        public override void ToStringBuilder(StringBuilder sb)
        {
            Left.ToStringBuilder(sb);
            sb.Append("\nleft outer join ");
            Right.ToStringBuilder(sb);
            sb.Append(" on ");
            OnExpression.ToStringBuilder(sb);
        }
    }
    internal class SqlInnerJoin : SqlTable
    {
        public SqlTable Left { get; private set; }
        public SqlTable Right { get; private set; }
        public SqlExpression OnExpression { get; private set; }
        public SqlInnerJoin(SqlTable left, SqlTable right, SqlExpression onExpression)
            : base(null, null, null)
        {
            this.Left = left;
            this.Right = right;
            this.OnExpression = onExpression;
        }
        public override void ToStringBuilder(StringBuilder sb)
        {
            Left.ToStringBuilder(sb);
            sb.Append("\ninner join ");
            Right.ToStringBuilder(sb);
            sb.Append(" on ");
            OnExpression.ToStringBuilder(sb);
        }
    }
    internal abstract class SqlSelect : SqlTable
    {
        public SqlSelect() :
            base(null, null, null)
        {
        }

        public static void ExecuteAndAttachToContext(ObjectContext ctx, QueryPlan queryPlan)
        {
#if PROFILE
            DateTime now = DateTime.Now;
            SqlQueryStats.QuerySetSize = sqlQuery.Queries.Count();
            SqlQueryStats.QueryCount++;
#endif
            var entityConnection = ctx.Connection as EntityConnection;
            var connectionString = entityConnection.StoreConnection.ConnectionString;            SqlCommand sqlCommand = new SqlCommand
            {
                CommandText = queryPlan.CommandText,
                Connection = new SqlConnection(connectionString)
            };
            sqlCommand.Connection.Open();
            foreach (var binding in queryPlan.Bindings)
            {
                var parameter = new SqlParameter(binding.Parameter.ToString(), binding.Value.ToString());
                sqlCommand.Parameters.Add(parameter);
            }
            using(var r = sqlCommand.ExecuteReader(CommandBehavior.Default))
            {
                foreach(var typeTree in queryPlan.TypeTrees)
                {
                    while(r.Read())
                    {
#if PROFILE
                        SqlQueryStats.RowCount++;
#endif
                        MakeAndAttachEntity(ctx, r, typeTree);
                    }
                    r.NextResult();
                }
            }
#if PROFILE
            SqlQueryStats.TotalQueryTime += DateTime.Now - now;
#endif
        }
        private static EntityKey MakeEntityKey(SqlDataReader r, TypeTree typeTree)
        {
            EntityKey entityKey;
            var keyMembers = typeTree.EntityType.KeyMembers;
            if(keyMembers.Count == 1)
            {
                var keyName = keyMembers[0].Name;
                var dbProp = keyName + typeTree.TypeId;
                entityKey = new EntityKey(typeTree.EntitySetName, keyName, r[dbProp]);
            }
            else
            {
                var entityKeyValues = new List<KeyValuePair<string, Object>>();
                foreach(var keyMember in keyMembers)
                {
                    var dbProp = keyMember.Name + typeTree.TypeId;
                    entityKeyValues.Add(new KeyValuePair<string, object>(keyMember.Name, r[dbProp]));
                }
                entityKey = new EntityKey(typeTree.EntitySetName, entityKeyValues);
            }
            return entityKey;
        }

        private static void MakeAndAttachEntity(ObjectContext ctx, SqlDataReader r, TypeTree rootNode)
        {
            // Obtain type from resultset and type tree
            var typeColumn = "type_" + rootNode.TypeId;
            var typeId = r[typeColumn];
            if(typeId == DBNull.Value)
                return;

            var typeNode = rootNode[(int)typeId];
            EntityKey entityKey = MakeEntityKey(r, rootNode);

            ObjectStateEntry entry;
            // Return if this entry is already contained in the object context.
            var isPresent = ctx.ObjectStateManager.TryGetObjectStateEntry(entityKey, out entry);
            if(isPresent == true)
            {
                return;
            }

            // Otherwise, create, instantiate and attach the type
            var entity = (IEntityWithKey)Activator.CreateInstance(typeNode.Type);
            // Set property values
            while(typeNode != null)
            {
                foreach(var edmProp in typeNode.Properties)
                {
                    var propInfo = typeNode.Type.GetProperty(edmProp.Name);
                    var dbProp = propInfo.Name + typeNode.TypeId;
                    var value = r[dbProp];
                    if(value != DBNull.Value)
                    {
                        propInfo.SetValue(entity, value, null);
                    }
                }
                typeNode = typeNode.ParentType;
            }
            ctx.AttachTo(rootNode.EntitySetName, entity);
#if PROFILE
            SqlQueryStats.AttachedEntitiesCount++;
#endif
            return;
        }
    }
    internal class SqlWhereExpression : SqlUnaryExpression
    {
        public SqlExpression Expression { get; private set; }
        public SqlWhereExpression(SqlExpression expression)
        {
            this.Expression = expression;
        }
        public override void ToStringBuilder(StringBuilder sb)
        {
            sb.Append("WHERE ");
            Expression.ToStringBuilder(sb);
        }
    }
    internal class SqlSelectColumns : SqlSelect
    {
        public List<SqlColumnSelect> Columns { get; set; }
        public SqlWhereExpression Where { get; set; }
        public SqlTable Table { get; set; }
        public SqlSelectColumns(List<SqlColumnSelect> columns, SqlTable table, SqlWhereExpression where)
        {
            this.Columns = columns;
            this.Table = table;
            this.Where = where;
        }
        public override void ToStringBuilder(StringBuilder sb)
        {
            sb.AppendLine("SELECT");
            for(int i = 0; i < Columns.Count; i++)
            {
                Columns[i].ToStringBuilder(sb);
                sb.Append(i + 1 < Columns.Count ? "," : null);
            }
            if(Table != null)
            {
                sb.AppendLine(" FROM");
                Table.ToStringBuilder(sb);
            }
            if(Where != null)
            {
                Where.ToStringBuilder(sb);
            }
            sb.AppendLine();
        }
    }
    internal class SqlNesting : SqlTable
    {
        public SqlTable Table { get; private set; }
        public SqlNesting(SqlTable table, string asName)
            : base(null, asName, null)
        {
            this.Table = table;
        }
        public override void ToStringBuilder(StringBuilder sb)
        {
            sb.Append("(");
            Table.ToStringBuilder(sb);
            sb.Append(") AS ");
            sb.Append(AsName);
        }
    }
    internal class SqlSelectFromTable : SqlSelect
    {
        public SqlWhereExpression Where { get; set; }
        public SqlTable Table { get; set; }
        public string FromTable { get; set; }
        public SqlSelectFromTable(string fromTable, SqlTable table, SqlWhereExpression where)
        {
            this.Table = table;
            this.Where = where;
            this.FromTable = fromTable;
        }
        public override void ToStringBuilder(StringBuilder sb)
        {
            sb.AppendFormat("SELECT {0}.* FROM ", FromTable);
            Table.ToStringBuilder(sb);
            sb.AppendLine();
            if(Where != null)
            {
                Where.ToStringBuilder(sb);
            }
            sb.AppendLine();
        }
    }
    internal class SQLParameter : SqlUnaryExpression
    {
        public string Name { get; private set; }
        public SQLParameter(string name)
        {
            this.Name = name;
        }
        public override void ToStringBuilder(StringBuilder sb)
        {
            sb.Append("@");
            sb.Append(Name);
        }
    }
    internal class SQLParameterBinding : Sql
    {
        public SQLParameter Parameter { get; private set; }
        public SqlConstantExpression Value { get; private set; }
        public SQLParameterBinding(SQLParameter parameter, SqlConstantExpression value)
        {
            this.Parameter = parameter;
            this.Value = value;
        }
        public override void ToStringBuilder(StringBuilder sb)
        {
            Parameter.ToStringBuilder(sb);
            sb.Append(" (");
            Value.ToStringBuilder(sb);
            sb.Append(")");
        }
    }
    internal class SqlSelectFromTableWithParameterBinding : SqlSelectFromTable
    {
        public SqlSelectFromTableWithParameterBinding(string fromTable, SqlTable table, SqlWhereExpression where)
            : base(fromTable, table, where)
        {
        }
        public SQLParameterBinding Bind(EntityGraphPath path, object entity)
        {
            if(this.Where != null)
            {
                if(((SqlEqualityExpression)this.Where.Expression).Rhs is SQLParameter)
                {
                    var leftNode = path[0];
                    var rightNode = path[1];
                    var left = leftNode.TypeTree.EntityType;
                    var right = rightNode.TypeTree.EntityType;

                    var nProp = left.NavigationProperties.Single(nprop => nprop.Name == leftNode.OutEdge.EdgeInfo.Name);
                    AssociationType associationType = (AssociationType)nProp.RelationshipType;
                    EntityType fromType = null;
                    EntityType toType = null;
                    EdmProperty fromProperty = null;
                    EdmProperty toProperty = null;
                    if(associationType.IsForeignKey)
                    {
                        var constraint = associationType.ReferentialConstraints.First();
                        fromType = constraint.FromRole.GetEntityType();
                        toType = constraint.ToRole.GetEntityType();
                        fromProperty = constraint.FromProperties.First();
                        toProperty = constraint.ToProperties.First();
                    }
                    else
                    {
                        fromType = ((RefType)associationType.KeyMembers[0].TypeUsage.EdmType).ElementType as EntityType;
                        fromProperty = fromType.KeyMembers[0] as EdmProperty;
                        toType = ((RefType)associationType.KeyMembers[1].TypeUsage.EdmType).ElementType as EntityType;
                        toProperty = fromType.KeyMembers[0] as EdmProperty;
                    }
                    var leftTableName = left.Name;
                    var leftPropertyName = left == toType ? toProperty.Name : fromProperty.Name;
                    var rightTableName = right.Name;
                    var rightPropertyName = right == toType ? toProperty.Name : fromProperty.Name;
                    var declaringType = leftNode.OutEdge.EdgeInfo.DeclaringType;
                    var value = declaringType.GetProperty(leftPropertyName).GetValue(entity, null);

                    var parameter = ((SqlEqualityExpression)this.Where.Expression).Rhs as SQLParameter;
                    return new SQLParameterBinding(parameter, new SqlConstantExpression(value));
                }
            }
            return null;
        }
    }
}
